package com.xzcs.util.db;

import com.xzcs.util.common.StringUtils;
import com.xzcs.util.spring.PropertyUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.TransactionCallbackWithoutResult;
import org.springframework.transaction.support.TransactionTemplate;

import java.sql.Connection;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 数据库操作类
 * @author chang
 * @createDate Aug 5, 2013
 * @description
 */
public class DBUtils extends JdbcDaoSupport {
	private static Logger logger = LoggerFactory.getLogger(DBUtils.class);
	public final static int DEFAULT_FETCHSIZE = 32;//默认的fetchsize
	public TransactionTemplate transactionTemplate;
	public StringUtils str = new StringUtils();
	
	public TransactionTemplate getTransactionTemplate() {
		return transactionTemplate;
	}

	public void setTransactionTemplate(TransactionTemplate transactionTemplate) {
		this.transactionTemplate = transactionTemplate;
	}

	public synchronized static DBUtils getInstance() {
		DBUtils db = null;
		try {
			db = (DBUtils) new PropertyUtils().getSpringBean("dbUtils");
		} catch (Exception e) {
			logger.error("创建数据操作对象失败！");
		}
		return db;
	}

	public synchronized static Connection getDBConnection() {
		Connection conn = null;
		DBUtils db = DBUtils.getInstance();
		try {
			conn = db.getConnection();
		} catch (Exception e) {
			logger.error(e.toString());
		}
		return conn;
	}

	/**
	 * 返回一条记录
	 * @param sql 传入的sql语句: select *from table where user_id=?
	 * @param objects
	 * @return
	 */
	public Map<String, Object> queryForMap(String sql, Object[] objects) {
		Map<String, Object> map = null;
		try {
			map = this.getJdbcTemplate().queryForMap(sql, objects);
		} catch (Exception e) {
			logger.error("queryForMap", e);
			logger.error(StringUtils.getSql(sql, objects));
		}
		if (map == null)
			map = new HashMap<String, Object>();
		return map;
	}
	
	/**
     * 返回一条记录 支持占位符 
     * @param sql 传入的sql语句: select *from table where user_id=?
     * @param list 查询时条件不确定，将条件放入一个List<String>中
     * @return
     */
    public Map<String, Object> queryForMap(String sql, List<String> list) {
        return this.queryForMap(sql, list.toArray());
    }
    
	public Map<String, Object> queryForMap(String sql) {
		return this.queryForMap(sql, (Object[]) null);
	}

	/**
	 * 获取某个字段的值
	 * @param sql
	 * @param args
	 * @return
	 */
	public String queryForString(String sql, Object[] args) {
		try {
			return StringUtils.notEmpty(this.getJdbcTemplate().queryForObject(sql, args, String.class));
		} catch (Exception e) {
			logger.error("queryForString", e);
			logger.error(StringUtils.getSql(sql, args));
			return "";
		}
	}

	public String queryForString(String sql) {
		return this.queryForString(sql, (Object[]) null);
	}
	
	public String queryForString(String sql, List<String> list) {
		return this.queryForString(sql, list.toArray());
	}
	
	/**
	 * 返回相应sequence的下一个值
	 * @param sequenceName sequence名称
	 * @return 下个值
	 */
	public String getNextSeqValue(String sequenceName) {
		Map<String, Object> map = null;
		String nextVal = "";
		try {
			map = this.queryForMap("select " + sequenceName + ".NEXTVAL SEQ from dual");
			nextVal = StringUtils.get(map, "SEQ");
		} catch (Exception e) {
			logger.error("getNextSeqValue", e);
		}
		return nextVal;
	}

	/**
	 * 返回数据集
	 * @param sql 传入的sql语句: select *from table where user_id=?
	 * @param objects
	 * @return
	 */
	public List<Map<String, Object>> queryForList(String sql, Object[] objects) {
		return this.queryForList(sql, objects, DEFAULT_FETCHSIZE);
	}
	
	/**
	 * 返回数据集
	 * 查询时条件不确定，将条件放入一个List<String>中
	 * @param sql
	 * @param list
	 * @return
	 */
	public List<Map<String, Object>> queryForList(String sql, List<String> list) {
		return this.queryForList(sql, list.toArray());
	}

	/**
	 * 查询条件不确定时返回数据集
	 * @param sql sql_where拼接 sql="select * from table where name='"+v_name+"'";
	 * @return
	 */
	public List<Map<String, Object>> queryForList(String sql) {
		return this.queryForList(sql, DEFAULT_FETCHSIZE);
	}
	
	/**
	 * 查询条件不确定时返回数据集
	 * @param sql sql_where拼接 sql="select * from table where name='"+v_name+"'";
	 * @param fetchSize 一次获取的数据条数
	 * @return
	 */
	public List<Map<String, Object>> queryForList(String sql, int fetchSize) {
		return this.queryForList(sql, (Object[]) null, fetchSize);
	}
	
	/**
	 * 返回数据集
	 * @param sql 传入的sql语句: select *from table where user_id=?
	 * @param objects
	 * @param fetchSize
	 * @return
	 */
	public List<Map<String, Object>> queryForList(String sql, Object[] objects, int fetchSize) {
		JdbcTemplate jdbc = this.getJdbcTemplate();
		jdbc.setFetchSize(fetchSize);
		
		List<Map<String, Object>> list = null;
		try {
			list = jdbc.queryForList(sql, objects);
		} catch (Exception e) {
			logger.error("queryForList", e);
			logger.error(StringUtils.getSql(sql, objects));
		}
		if (list == null)
			list = new ArrayList<Map<String, Object>>();
		return list;
	}
	
	/**
	 * 返回数据集
	 * 查询时条件不确定，将条件放入一个List<String>中
	 * @param sql
	 * @param list
	 * @param fetchSize
	 * @return
	 */
	public List<Map<String, Object>> queryForList(String sql, List<String> list, int fetchSize) {
		return this.queryForList(sql, list.toArray(), fetchSize);
	}

	/**
	 * insert,update,delete 操作
	 * @param sql 传入的语句 sql="insert into tables values(?,?)";
	 * @param objects
	 * @return 0:失败 1:成功
	 */
	public int update(String sql, Object[] objects) {
		int exc = 1;
		try {
			this.getJdbcTemplate().update(sql, objects);
		} catch (Exception e) {
			exc = 0;
			logger.error("update", e);
			logger.error(StringUtils.getSql(sql, objects));
		}
		return exc;
	}

	public int update(String sql) {
		int exc = 1;
		try {
			this.getJdbcTemplate().update(sql);
		} catch (Exception e) {
			exc = 0;
			logger.error(sql);
			logger.error("update", e);
		}
		return exc;
	}

	/**
	 * 返还记录数
	 * @param sql 传入的sql语句 select count(*) from table where name=?
	 * @param objects 参数值
	 * @return -1:数据库异常
	 */
	public int queryForInt(String sql, Object[] objects) {
		int exc = -1;
		try {
			exc = this.getJdbcTemplate().queryForObject(sql, objects, Integer.class);
		} catch (Exception e) {
			exc = -1;
			logger.error("queryForInt", e);
			logger.error(StringUtils.getSql(sql, objects));
		}
		return exc;
	}
	
	/**
	 * 返还记录数
	 * @param sql 传入的sql语句 select count(*) from table where name=?
	 * @param list 参数值
	 * @return -1:数据库异常
	 */
	public int queryForInt(String sql, List<String> list) {
		return this.queryForInt(sql, list.toArray());
	}

	/**
	 * 返还记录数
	 * @param sql 传入的sql语句直接拼接好
	 * @return
	 */
	public int queryForInt(String sql) {
		return this.queryForInt(sql, (Object[]) null);
	}

	/**
	 * 事务处理
	 * @param batchSql
	 * @return
	 */
	public int doInTransaction(final BatchSql batchSql) {
		int exc = 1;
		if (batchSql == null) {
			exc = 0;
		}
		try {
			transactionTemplate.execute(new TransactionCallbackWithoutResult() {
				public void doInTransactionWithoutResult(
						TransactionStatus status) {
					List<Map<String, Object>> sqlList = batchSql.getSqlList();
					for (Map<String, Object> map : sqlList) {
						String sql = (String) map.get("sql");
						Object[] objects = (Object[]) map.get("objects");
						getJdbcTemplate().update(sql, objects);
					}
				}
			});
		} catch (Exception e) {
			exc = 0;
			logger.error("doInTransaction", e);
		}
		return exc;
	}

	public ProcedureUtils getProcUtils(String sql) {
		ProcedureUtils proc = null;
		try {
			proc = new ProcedureUtils(this.getDataSource(), sql);
			proc.setSql(sql);
		} catch (Exception e) {
			logger.error(e.toString());
		}
		return proc;
	}
}
